Commands¶

control_create_account¶

To make a new Cubed account you will need to have a database configured first with the correct user added: attribroot + usual password.

You will need the following details to hand:

Database connection string
Client domain
Client name
Agreed token - for taggers to implement
Agreed DB name

When you run this command you will be greeted with a friendly user input window, please follow the onscreen instructions.

Example¶

Here are some example details and what the command output would look like.

variable	value	notes
client domain	https://www.test.com	This is the whole website but we will only need the domain and tld
client name	test	friendly name for the dashboard
token	c-a-test-uk	this will be used by taggers and across cubed to help identify this account
DB name	cubed_a_test_uk	All DBs follow this naming convetion
db connection string	mysql://username:password@192.168.0.1:3306/cubed_a_test_uk	Complete URL with DB name

After inputting these pieces of data you will be asked the following questions:

Would you like to add all staff users? (Currently: No): [Y/n]
> "yes" - a cubed client should be accessible by all staff
Would you like to create visscore systems? (Currently: No): [Y/n]
> "yes" - this will create the account specific SQS in AWS
Would you like to add aws config? (Currently: No): [Y/n]
> "yes" - it adds configs to spin up boxes for mba, gtrends, and others.

Warning

The output says (Currently: No) [Y/n], the "no" is a bug, please pay attention to the capitalized letters. Y=yes, N=no.

Below is an example output of using these values:

Choose a token (name-location), example: company-uk, this will become 'c-a-company-uk'
Token (default test): test-uk
Connection: (example: mysql://{{username}}:{{password}}@{{host-name}}:{{port}}/cubed_a_test_uk)mysql://username:password@192.168.0.1:3306/cubed_a_test_uk
Domain (please include extension, for example: .co.uk or .com): test.com
Would you like to add all staff users? (Currently: No):  [Y/n]y
Would you like to create visscore systems? (Currently: No):  [Y/n]y
Would you like to add aws config? (Currently: No):  [Y/n]y

Note

"create visscore systems" is legacy and needs to be removed from this step.

Once you've finished, read the final output carefully and type Y or N to continue or cancel. Which will look like this:

-------
Creating account with the following details:

Account Name: Test
Account Token: c-a-test-uk
Database Name: cubed_a_test_uk
Connection: mysql://username:password@192.168.0.1:3306/cubed_a_test_uk
Domain: client.com

-------
Extra Config:
Add all users: True
Create Visscore Systems: True
Add AWS Config: True

--------
To toggle these via command line, please call the command again and pass:

--add_users
--create_visscore_systems
--add_aws_config

-------

Build account with these details? (please check above!) [Y/n]

update_visit_subchannels¶

This command uses the SubchannelRuleset utility function to build and execute rulesets which affect visit data to change which subchannel they are associated with. Rules are created in the SubchannelRuleset class itself and are evaluated based on the function name. This provides update_visit_subchannels with a list of rules to execute.

Below the component parts of the command are described in more detail.

handle_client_prompt¶

update_visit_subchannels takes an option argument --channel, which defaults to all if not set. This can be used to set the specific channel from attrib_referer(sic) for which the command should run. update_visit_subchannels extends DateClientPromptCommand.

The command begins by retrieving all Referer objects; optionally filtered by the string passed with --channel.

From there, the command cycles through each Referer object and retrieves any associated Visit objects for the day in question filtered on pattern.referer¹. Each Visit is mapped with the id of the Generic subchannel that matches that referer². This is stored in the sub_channels dict, which will become important later! For reference, the dict looks like this, assuming the Generic subchannel for our referer has an ID of 1 and we have two Visits with ID 1 and 2 in our time period:

{
    1: 1, // Visit 1, Referer 1 
    2: 1 // Visit 2, Referer 1
}

After this, we get all SubchannelRule objects for that referer, ordered by priority. For each object, we look to see if there is a rule set up for it by filtering SubchannelRuleset.functions by three keys: referer name, rule category, and rule key. The function is then called with the --startdate and --enddate from DateClientPromptCommand.

This is quite a complicated lookup that spans several tables, so here is an example to explain further.

For this Referer object:

id	name	active
45	Affiliates	1

And this Subchannel object:

id	name	active	referer_id
16	Adult	1	45

This SubchannelRule...:

id	category	key	value	is_regex	active	subchannel_id
69	adwords	campaign_name	`myProduct1`	0	1	16

...would give us the keywords 'affiliates', 'adwords' and 'campaign_name'.

'Affiliates' is taken from Referer.name, which is connected to via subchannel_id 16, which lets us know that the referer_id is 45, giving us 'affiliates'.

Far more simply, 'adwords' is from SubchannelRule.category, and 'campaign_name' is from SubchannelRule.key.

So, the function we would return based on this is rule__affiliates__adwords__campaign_name. Each component is separated by two underscores __. By way of summary:

component	derived from
rule	required, always present
affiliates	`SubchannelRule.subchannel.referer.name`
adwords	`SubchannelRule.category`
campaign_name	`SubchannelRule.key`

A SubchannelRule object will specify a string value to be used to identify matching visits. Optionally, we can also set an is_regex flag to denote that the value is a regex. If this flag is set, the regex is compiled before calling the function. Some processing is also done on strings to ensure they are safe and match our current syntax.

    def rule__shopping__adwords_shopping__product_type1(self, startdate, enddate):      
        data = AdwordsAggShoppingVisit.objects\
            .filter(visit__first_visit__gte=startdate, visit__first_visit__lte=enddate)\
            .values('product_type1', 'visit')\
            .distinct()

        return group_data(data, 'product_type1', 'visit')

^{an example of a complete rule}

So where startdate='2021-08-10 00:00:00 and enddate='2021-08-11 00:00:00, we would expect the following rows to be returned from the AdwordsAggShoppingVisit table (truncated for brevity):

id	gclid	offer_id	campaign_id	ad_group_id	product_type1	visit_id
1	12345	1	2	3	myProduct1	1
2	54321	4	5	6	myProduct1	2

From this, we know that the Visit objects with IDs 1 and 2 must have first_visit values of between our given startdate and enddate as this is what we are passing in our filter function.

The data being returned can normally be found in the function name (eg rule__shopping__adwords_shopping__product_type1 retrives values from product_type1).

The function itself must return a dictionary, which is created by calling the group_data utility function and passing it the returned data, the field it should be grouped by, and the relevant visit path that is to be assessed. Remember that each function must deal with a model that can be linked back to a Visit model so that the visit_path lookup can function.

def group_data(data, group_by, visit_path): ^{the group_data function declaration and it's required arguments}

The queryset passed to group_data will be flattened into a dict grouped by the key from SubchannelRule.key, and will look something like this:

{
    'myProduct1': {
        'visits': [1, 2]
    },
    'myProduct2': {
        'visits': [3, 4]
    }
}

Note that 'myProduct1' would be the value derived from product_type1, which we passed to group_data and retrieved the values for from AdwordsAggShoppingVisit.product_type1 in the function above. The list of visits corresponds to the visits which are to be assessed by our SubchannelRule, which we gathered above.

From here, the returned data is filtered based on the value previously provided by the SubchannelRule. Remember this can be done either using a regex or a simple string lookup. This is done by the filter_matched_options function, which compares the keys from the data above ('myProduct1') with the value from SubchannelRule.value.

If there is a match, the key and it's data are retained, else they are deleted. In our example, we can see that visits associated with 'myProduct2' have been filtered because our rule is only concerned with 'myProduct1'.

Finally, the subchannel id (16 here) is appended to the dictionary. So from the data above, we would end up with a data structure similar to the following:

{
    'myProduct1': {
        {'visits': [1,2], 'rule_id': 16}
    }
}

Note that rule_id is a misleading key here - 16 is in fact a subchannel ID.

Next, we return to the sub_channels dict we created earlier. Here, for every visit we have identified above (1 and 2 in our example) we substitute the generic subchannel ID with which we instantiated the dict with the new subchannel as specified by SubchannelRule above. So, compared to sub_channels above, we now have a dict that looks like this:

{1: 16, 2: 16}

And finally, this data is used to update the AggVisitSubchannel model, to bind the visits we found to the subchannel specified in our rule. We would expect the data to have gone from something like this:

id	subchannel_id	visit_id
1	1	1
1	1	2

To this:

id	subchannel_id	visit_id
1	16	1
1	16	2

SubchannelRuleset¶

This class is the heart of the above command; this is the only placed it is used in the project. It has two properties: .options and .functions. .options returns a list of available function names within the class, nested by level - for example:

{
    "affiliates": {
        "adwords": {
            "campaign_name": "campaign_name",
            "landing_page_path": "landing_page_path",
            ...
        }
    }
}

This shows the referer data that will be targetted - 'affiliates' here. Other options might be SEO, PPC or Display, for example. A matching entry will need to appear in attrib_referer before a rule is created for them.

'Adwords' relates back directly to attrib_subchannel_rule.category - the category of the referrer data that will be targetted. Similarly 'campaign_name' is the key that will used to help identify the rule to be used. The matching pair value will always match the key for consistency purposes.

Creating your own rule¶

It's highly likely that if you're reading this what you're actually interested in is creating your own rules.

Assumption: you already have data set up and being captured that you wish to reclassify into a different subchannel.

Set up your subchannel rule

Initially none of this will do anything as there won't be a matching function. You will need to identify:

for which Referer (channel) this rule will run
what keyword you will use for your category
which model you will be using to identify relevant visits
how that model links back to a Visit object
whether or not you will be using a regex
which subchannel you wish the visits to be associated with

From this, you should be able to build your rule SubchannelRule object, which will look like this:

id	category	key	value	is_regex	priority	created	updated	active	subchannel_id
1	adwords	campaign_name	`Brand`	0	255	{datetime}	{datetime}	1	16

A reminder of how you should populate each column:

column	description
id	auto-increment
category	category your rule falls into
key	the column name in the table you wish to use to find relevant visits
value	the value you wish to match on to denote relevant visits
is_regex	whether your value should be assessed as a regular expression
priority	in theory sets a priority for clashing rules, in practice not used
created	datetime stamp
updated	datetime stamp
active	whether the command should assess the rule
subchannel_id	the ID of the subchannel you wish to bind your visits to

Add the corresponding function to /backend/client/utils.py

For your function to be found by the update_visit_subchannels command, remember you need to name it using this nomenclature:

component derived from

rule required, always present

affiliates SubchannelRule.subchannel.referer.name

adwords SubchannelRule.category

campaign_name SubchannelRule.key

So for the above example, we would expect a function name of: rule__affiliates__adwords__campaign_name.

You can pass any arguments you need to your function (as long as they have the correct context in the command) but you must pass startdate and enddate. You can also base your queryset on any object as long as it has an eventual link to a Visit object.

Remember to only return the values you need and not entire objects. One of the values must be the id of the visit associated with the model you are assessing.

Ensure you are using the group_data utility function to return your data.
Ensure your data is set up correctly

The rule we have just created will be touching at least 6 models. When developing (and testing) you need to ensure that all of your data is present and correct.
Test thoroughly

Either by manual or automated testing.

^{1. Remember that Visits are foreign keyed to Patterns, which are in turn foreign keyed to Referers.}

^{2. All Referers have the Generic subchannel set up for them as part of onboarding, so we know it will be present when looked for.}

update_adwords_agg_shopping_visit¶

Calls the sp_adwords_agg_shopping_visit procedure passing in a start date and an end date as parameters. Prompts the user for confirmation before execution.

backdate_report_tables¶

Calls commands: - update_report_landing_page_params - update_report_utm_params - update_report_referring_page_params - update_report_shopping_params - update_report_adwords_params

Sequentially with prompts for user confirmation between each execution. If --setdate is passed, --startdate and --enddate must be used to specify a custom date range. If --setdate is not passed then the start and end date will become the first and last visits of the client.

arguments:¶

`--startdate`¶

`--enddate`¶

`--setdate`¶

Example: python manage.py backdate_report_tables --setdate --startdate="YYYY-MM-DD HH:MM:SS" --enddate="YYYY-MM-DD HH:MM:SS"

migrate_client¶

Synchronises the client database state with the current set of models and migrations.

`--waitfor X`¶

Waits X amount of seconds after the user prompt to execute the migration.

`--loaddata`¶

Searches and loads the contents of the named fixture into the database.

Requires --database to be true.

`--database`¶

Specifies the database into which the data will be loaded.

`--fake`¶

Marks the migrations up to the target one as applied, but without actually running the SQL to change the database schema.

`--fake-inital`¶

Allows Django to skim the app's initial migration if all database tables with the names of all models created by all CreateModel operations in that migration already exist.

`--merge`¶

Enables fixing of migration conflicts.

`--plan`¶

Shows the migration operations that will be performed for the given migrate command.

Commands¶

control_create_account¶

Example¶

update_visit_subchannels¶

handle_client_prompt¶

SubchannelRuleset¶

Creating your own rule¶

update_adwords_agg_shopping_visit¶

backdate_report_tables¶

arguments:¶

--startdate¶

--enddate¶

--setdate¶

migrate_client¶

--waitfor X¶

--loaddata¶

--database¶

--fake¶

--fake-inital¶

--merge¶

--plan¶